﻿/*
INSTALL AS A POST DEPLOYMENT SCRIPT
*/

PRINT 'Running Todoc...'

SET NOCOUNT ON;

/*
--------------------------------------------------------------------------------------
RUN ALL TODOC-ENABLED TABLES THROUGH THE ADMINISTER ROUTINE

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
--------------------------------------------------------------------------------------
*/

DECLARE @TableSchema SYSNAME;
DECLARE @TableName SYSNAME;

--EXECUTE AS USER = 'todocuser';

--IF (CHARINDEX('SQL2012DEV01', @@SERVERNAME)) > 0
--BEGIN

	DECLARE TodocTables CURSOR
	READ_ONLY FORWARD_ONLY LOCAL STATIC
	FOR
	SELECT	nht.TableSchema, 
			nht.TableName
	FROM [todoc].[svNonHeapTables] nht
	INNER JOIN [todoc].[svActiveTables] at
		ON nht.[schema_id] = at.[schema_id]
		AND nht.[object_id] = at.[object_id]
	WHERE at.TodocEnabled = 'True'
		AND at.TodocRun <> 'True'
	ORDER BY nht.TableSchema, nht.TableName;

	OPEN TodocTables

	PRINT 'Administering todoc objects for:'

	FETCH NEXT FROM TodocTables INTO @TableSchema, @TableName
	WHILE (@@fetch_status <> -1)
	BEGIN
		IF (@@fetch_status <> -2)
		BEGIN

			PRINT @TableSchema + '.' + @TableName;

			-- run whatever todoc is required for this table
			BEGIN TRY
				EXEC todoc.[sspAdministerObjects] 
					@TableSchema = @TableSchema, 
					@TableName = @TableName;
			END TRY
			BEGIN CATCH
				SELECT ERROR_MESSAGE();
				ROLLBACK TRAN;
			END CATCH

		END
		FETCH NEXT FROM TodocTables INTO @TableSchema, @TableName
	END

	CLOSE TodocTables
	DEALLOCATE TodocTables;

--END

--REVERT;
GO